package com.five.util;


import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: 王登峰
 * @Description:MicroWin10-1840
 * @Date: 2020/11/4 18:22
 */
public class DBUtil {
    public static String DRIVER ="com.mysql.jdbc.Driver";
    public static String URL ="jdbc:mysql://MicroWin10-1840/hotel?useUnicode=true&useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
    public static String USER = "root";
    public static String PASSWORD = "123456";
//    public static String PASSWORD = "wdf961010";

    //加载驱动
    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConn(){
        Connection conn = null ;
        try {
           conn =  DriverManager.getConnection(URL,USER,PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //关闭连接
    public static void close(ResultSet rs , PreparedStatement ps,Connection conn){
        try {
            if(rs != null)
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(ps != null)
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null)
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //查询返回list list
    public static List<List> queryList(String sql , Object... obj){
        Connection conn = getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<List> show = new ArrayList<>();
        try {
            ps = conn.prepareStatement(sql);
            if(obj.length > 0){
                for(int i =1 ; i <= obj.length ; i++){
                    ps.setObject(i,obj[i-1]);
                }
            }
            rs = ps.executeQuery();
            while(rs.next()){
                List list = new ArrayList();
                for(int i =1 ; i <= rs.getMetaData().getColumnCount(); i++)
                    list.add(rs.getObject(i));
                show.add(list);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(rs,ps,conn);
        }
        return show;
    }

    //查询返回list Map
    public static List<Map> queryMap(String sql , Object... obj){
        Connection conn = getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map> show = new ArrayList<>();
        try {
            ps = conn.prepareStatement(sql);
            if(obj.length > 0){
                for(int i =1; i <= obj.length; i++)
                    ps.setObject(i,obj[i-1]);
            }
            rs = ps.executeQuery();
            while (rs.next()){
                HashMap<Object, Object> map = new HashMap<>();
                for(int i =1; i <= rs.getMetaData().getColumnCount(); i++)
                    map.put(rs.getMetaData().getColumnLabel(i),rs.getObject(i));
                show.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(rs,ps,conn);
        }
        return show;
    }

    //新增返回主键
    public static int getKey(String sql, Object... obj){
        Connection conn = getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            if(obj.length >0){
                for(int i = 1; i <= obj.length; i++)
                    ps.setObject(i, obj[i-1]);
            }
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            if(rs.next())
                return rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(rs,ps,conn);
        }
        return -1;
    }

    //增删改
    public static int manage(String sql, Object... obj){
        Connection conn = getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            if(obj.length > 0){
                for(int i = 1; i <= obj.length; i++)
                    ps.setObject(i, obj[i-1]);
            }
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(rs,ps,conn);
        }
        return -1;
    }
    /**
     * 调用只含有in的存储过程
     */
    public static void getCall(String sql){
        Connection con = null;
        CallableStatement callstatement=null;
        try {
            con = getConn();
            callstatement= con.prepareCall(sql);
//        if(obj.length>0){
//            for(int i=0;i<obj.length;i++){
//                //第一个参数代表的是占位符是第几个   第二个参数 赋的值
//                callstatement.setObject(i+1,obj[i]);
//            }
//        }
            //callstatement.registerOutParameter(1, java.sql.Types.INTEGER);//注册存储过程的out型参数类型；使用之前必须注册；
            callstatement.execute();//执行
            //System.out.println(callstatement.getInt(1));//获得存储过程的输出参数
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(null,callstatement,con);
        }
        //return null;
    }

    /**
     *函数有输出参数的存储过程
     */
    public static List<String> getCallOut(String sql,Object... obj){
        List<String> list=new ArrayList<>();
        Connection con = null;
        CallableStatement callstatement=null;
        try {
            con = getConn();
            callstatement= con.prepareCall(sql);
            if(obj.length>0){
                for(int i=0;i<obj.length;i++){
                    //第一个参数代表的是占位符是第几个   第二个参数 赋的值
                    callstatement.registerOutParameter(i+1, Types.VARCHAR);
                }
            }
            //callstatement.registerOutParameter(1, java.sql.Types.INTEGER);//注册存储过程的out型参数类型；使用之前必须注册；
            callstatement.execute();//执行
            for(int i=0;i<obj.length;i++){
                //第一个参数代表的是占位符是第几个   第二个参数 赋的值
                // callstatement.registerOutParameter(i+1, Types.VARCHAR);
                //System.out.println("剩余的房间数："+callstatement.getString(i+1));
                if(callstatement.getString(i+1)!=null){
                    list.add(callstatement.getString(i+1));
                }

            }
            //System.out.println(callstatement.getInt(1));//获得存储过程的输出参数
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(null,callstatement,con);
        }
        return list;
    }
    @Test
    public void  ss(){
        System.out.println("撒大");
    }
}
